﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using System.Configuration;
using Intergaces;
using DomainModel;


namespace SqlRepository
{
	public class SqlRepository : IImageRepository
	{
		public SqlRepository()
		{
		}

		public IEnumerable<IImage> Load(int start,int end)
		{

            var _connection = new SqlConnection(GetPathToDataBase());

            var ListImage = new List<IImage>();

            try
            {
                var command = new SqlCommand
                {
                    Connection = _connection,
                    CommandType = CommandType.StoredProcedure,
                    CommandText = "SaveImage"//change name of stored procedure
                };

                #region formatCommand

                command.Parameters.AddWithValue("@start", start);
                command.Parameters["@start"].DbType = DbType.Int32;
                command.Parameters.AddWithValue("@end", end);
                command.Parameters["@end"].DbType = DbType.Int32;

                #endregion

                _connection.Open();

                SqlDataReader reader=command.ExecuteReader();


                while (reader.Read())
                {
                    ListImage.Add(new ImageEntity() { Id = (Guid)reader["id"], DataFull = (byte[])reader["fullSizeImage"], DataSmall = (byte[])reader["smallSizeImage"] });
                }

            }
            catch (SqlException e)
            {
                throw new Exception("Expception in db",e);
            }
            catch (Exception e)
            {
                //   log.Error("Error DAL", e);
                throw new Exception("Expception in DAL",e);
            }
            finally
            {
                _connection.Close();
            }

			return ListImage;
		}

		protected string GetPathToDataBase()
		{
			var connectionSettingString = ConfigurationManager.ConnectionStrings["SQLConnection"];
			return connectionSettingString.ConnectionString;
		}

		public void Save(IImage image)
		{
			var _connection = new SqlConnection(GetPathToDataBase());

			try
			{
				var command = new SqlCommand
				{
					Connection = _connection,
					CommandType = CommandType.StoredProcedure,
					CommandText = "SaveImage"
				};

				#region formatCommand

				command.Parameters.AddWithValue("@id", image.Id);
				command.Parameters["@id"].DbType = DbType.Guid;
				command.Parameters.AddWithValue("@fullSizeImage", image.DataFull);
				command.Parameters["@fullSizeImage"].DbType = DbType.Binary;
				command.Parameters.AddWithValue("@smallSizeImage", image.DataSmall);
				command.Parameters["@smallSizeImage"].DbType = DbType.Binary;

				#endregion

				_connection.Open();

				command.ExecuteNonQuery();


			}
            catch (SqlException e)
            {
                throw new Exception("Expception in db", e);
            }
            catch (Exception e)
            {
                //   log.Error("Error DAL", e);
                throw new Exception("Expception in DAL", e);
            }
			finally
			{
				_connection.Close();
			}
		}

		public int GetImageCount()
		{

			int count = 0;
			var _connection = new SqlConnection(GetPathToDataBase());

			try
			{
				var command = new SqlCommand
				{
					Connection = _connection,
					CommandType = CommandType.StoredProcedure,
					CommandText = "ImageCount"
				};


				_connection.Open();

				count = (int)command.ExecuteScalar();


			}
            catch (SqlException e)
            {
                throw new Exception("Expception in db", e);
            }
            catch (Exception e)
            {
                //   log.Error("Error DAL", e);
                throw new Exception("Expception in DAL", e);
            }
			finally
			{
				_connection.Close();

			}
			return count;
		}
        public IEnumerable<IImage> GetIDs(int start, int end)
        {
            var _connection = new SqlConnection(GetPathToDataBase());

            var ListID = new List<IImage>();

            try
            {
                var command = new SqlCommand
                {
                    Connection = _connection,
                    CommandType = CommandType.StoredProcedure,
                    CommandText = "GetRangeOfID"
                };

                #region formatCommand

                command.Parameters.AddWithValue("@start", start);
                command.Parameters["@start"].DbType = DbType.Int32;
                command.Parameters.AddWithValue("@end", end);
                command.Parameters["@end"].DbType = DbType.Int32;
                #endregion

                _connection.Open();

                SqlDataReader reader = command.ExecuteReader();


                while (reader.Read())
                {
									ListID.Add(new ImageEntity { Id = (Guid)reader["id"] });
                }

            }
            catch (SqlException e)
            {
                throw new Exception("Expception in db", e);
            }
            catch (Exception e)
            {
                //   log.Error("Error DAL", e);
                throw new Exception("Expception in DAL", e);
            }
            finally
            {
                _connection.Close();
            }

            return ListID;
        }

        public byte[] LoadImageFull(Guid id)
        {
            return GetImage("GetImageBig", id);
        }

        public byte[] LoadImageLittle(Guid id)
        {
            return GetImage("GetImageLittle", id);
        }

        protected byte[] GetImage(string nameStoredProcedure,Guid id)
        {
            var _connection = new SqlConnection(GetPathToDataBase());

            var ListID = new List<Guid>();
            byte[] image = null;
            try
            {
                var command = new SqlCommand
                {
                    Connection = _connection,
                    CommandType = CommandType.StoredProcedure,
                    CommandText = nameStoredProcedure
                };

                #region formatCommand

                command.Parameters.AddWithValue("@id", id);
                command.Parameters["@id"].DbType = DbType.Guid;

                #endregion

                _connection.Open();

                image = (byte[])command.ExecuteScalar();

            }
            catch (SqlException e)
            {
                throw new Exception("Expception in db", e);
            }
            catch (Exception e)
            {
                //   log.Error("Error DAL", e);
                throw new Exception("Expception in DAL", e);
            }
            finally
            {
                _connection.Close();
            }

            return image;
        }

      
	}
}
